{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hybrid data representation using Blaze"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the Blaze."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import blaze as bl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Abstract data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with NumPy array"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a simple NumPy array: we first load NumPy and then create a matrix with two rows and three columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "simpleArray = np.array([\n",
    "        [1,2,3],\n",
    "        [4,5,6]\n",
    "    ])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have an array we can abstract it with Blaze's Data structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "simpleData_np = bl.Data(simpleArray)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to peek *inside* the structure you can either use `.peek()` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1, 2, 3],\n",
       "       [4, 5, 6]])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simpleData_np.peek()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or use (familiar to those of you versed in pandas' syntax) the `.head(...)` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "array([[1, 2, 3]])"
      ],
      "text/plain": [
       "array([[1, 2, 3]])"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simpleData_np.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to retrieve the first *column* you can use indexing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>None</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    \n",
       "0  1\n",
       "1  2\n",
       "2  3"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simpleData_np[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to retrieve columns you have to transpose your DataShape."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>None</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    \n",
       "0  1\n",
       "1  4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simpleData_np.T[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's specify names of our *fields*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "simpleData_np = bl.Data(simpleArray, fields=['a', 'b', 'c'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now retrieve the data simply by calling the column by it's name; let's retrieve column `'b'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   b\n",
       "0  2\n",
       "1  5"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simpleData_np['b']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Start by importing `pandas`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we create a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "simpleDf = pd.DataFrame([\n",
    "        [1,2,3],\n",
    "        [4,5,6]\n",
    "    ], columns=['a','b','c'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and transform it into a DataShape."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "simpleData_df = bl.Data(simpleDf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can retrieve data in the same manner as with the DataShape created from the NumPy array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   a\n",
       "0  1\n",
       "1  4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simpleData_df['a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Working with files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataShapes can be created directly from a CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import odo\n",
    "\n",
    "traffic = bl.Data('../Data/TrafficViolations.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To save the data into multiple archives (for each year of traffic violation) use this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for year in traffic.Stop_year.distinct().sort():\n",
    "    odo.odo(traffic[traffic.Stop_year == year], \n",
    "        '../Data/Years/TrafficViolations_{0}.csv.gz'\\\n",
    "        .format(year))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you do not know the names of columns in any dataset, you can get these from the DataShape."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Stop_month', 'Stop_day', 'Stop_year', 'Stop_hr', 'Stop_min', 'Stop_sec', 'Agency', 'SubAgency', 'Description', 'Location', 'Latitude', 'Longitude', 'Accident', 'Belts', 'Personal_Injury', 'Property_Damage', 'Fatal', 'Commercial_License', 'HAZMAT', 'Commercial_Vehicle', 'Alcohol', 'Work_Zone', 'State', 'VehicleType', 'Year', 'Make', 'Model', 'Color', 'Violation_Type', 'Charge', 'Article', 'Contributed_To_Accident', 'Race', 'Gender', 'Driver_City', 'Driver_State', 'DL_State', 'Arrest_Type', 'Geolocation']\n"
     ]
    }
   ],
   "source": [
    "print(traffic.fields)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Blaze can also read directly from a GZipped archives."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "traffic_gz = bl.Data('../Data/TrafficViolations.csv.gz')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "To compare we get exactly the same data let's retrieve the first two records from each structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_month</th>\n",
       "      <th>Stop_day</th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Stop_hr</th>\n",
       "      <th>Stop_min</th>\n",
       "      <th>Stop_sec</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal_Injury</th>\n",
       "      <th>Property_Damage</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Commercial_License</th>\n",
       "      <th>HAZMAT</th>\n",
       "      <th>Commercial_Vehicle</th>\n",
       "      <th>Alcohol</th>\n",
       "      <th>Work_Zone</th>\n",
       "      <th>State</th>\n",
       "      <th>VehicleType</th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Color</th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed_To_Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver_City</th>\n",
       "      <th>Driver_State</th>\n",
       "      <th>DL_State</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Geolocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2014</td>\n",
       "      <td>23</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>DRIVER FAILURE TO STOP AT STEADY CIRCULAR RED ...</td>\n",
       "      <td>PARK RD AT HUNGERFORD DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FORD</td>\n",
       "      <td>MUSTANG</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Citation</td>\n",
       "      <td>21-202(h1)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>M</td>\n",
       "      <td>ROCKVILLE</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>2015</td>\n",
       "      <td>23</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>HEADLIGHTS (*)</td>\n",
       "      <td>CONNECTICUT AT METROPOLITAN AVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>HONDA</td>\n",
       "      <td>2S</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>ESERO</td>\n",
       "      <td>55*</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>SILVER SPRING</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_month  Stop_day  Stop_year  Stop_hr  Stop_min  Stop_sec Agency  \\\n",
       "0           9        30       2014       23        51         0    MCP   \n",
       "1           3        31       2015       23        59         0    MCP   \n",
       "\n",
       "                 SubAgency                                        Description  \\\n",
       "0  1st district, Rockville  DRIVER FAILURE TO STOP AT STEADY CIRCULAR RED ...   \n",
       "1   2nd district, Bethesda                                     HEADLIGHTS (*)   \n",
       "\n",
       "                          Location     ...          Charge  \\\n",
       "0         PARK RD AT HUNGERFORD DR     ...      21-202(h1)   \n",
       "1  CONNECTICUT AT METROPOLITAN AVE     ...             55*   \n",
       "\n",
       "                  Article Contributed_To_Accident      Race Gender  \\\n",
       "0  Transportation Article                      No     BLACK      M   \n",
       "1                     NaN                      No  HISPANIC      M   \n",
       "\n",
       "     Driver_City Driver_State DL_State        Arrest_Type Geolocation  \n",
       "0      ROCKVILLE           MD       MD  A - Marked Patrol         NaN  \n",
       "1  SILVER SPRING           MD       MD  A - Marked Patrol         NaN  \n",
       "\n",
       "[2 rows x 39 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_month</th>\n",
       "      <th>Stop_day</th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Stop_hr</th>\n",
       "      <th>Stop_min</th>\n",
       "      <th>Stop_sec</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal_Injury</th>\n",
       "      <th>Property_Damage</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Commercial_License</th>\n",
       "      <th>HAZMAT</th>\n",
       "      <th>Commercial_Vehicle</th>\n",
       "      <th>Alcohol</th>\n",
       "      <th>Work_Zone</th>\n",
       "      <th>State</th>\n",
       "      <th>VehicleType</th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Color</th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed_To_Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver_City</th>\n",
       "      <th>Driver_State</th>\n",
       "      <th>DL_State</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Geolocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2014</td>\n",
       "      <td>23</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>DRIVER FAILURE TO STOP AT STEADY CIRCULAR RED ...</td>\n",
       "      <td>PARK RD AT HUNGERFORD DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FORD</td>\n",
       "      <td>MUSTANG</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Citation</td>\n",
       "      <td>21-202(h1)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>M</td>\n",
       "      <td>ROCKVILLE</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>2015</td>\n",
       "      <td>23</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>HEADLIGHTS (*)</td>\n",
       "      <td>CONNECTICUT AT METROPOLITAN AVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>HONDA</td>\n",
       "      <td>2S</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>ESERO</td>\n",
       "      <td>55*</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>SILVER SPRING</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_month  Stop_day  Stop_year  Stop_hr  Stop_min  Stop_sec Agency  \\\n",
       "0           9        30       2014       23        51         0    MCP   \n",
       "1           3        31       2015       23        59         0    MCP   \n",
       "\n",
       "                 SubAgency                                        Description  \\\n",
       "0  1st district, Rockville  DRIVER FAILURE TO STOP AT STEADY CIRCULAR RED ...   \n",
       "1   2nd district, Bethesda                                     HEADLIGHTS (*)   \n",
       "\n",
       "                          Location     ...          Charge  \\\n",
       "0         PARK RD AT HUNGERFORD DR     ...      21-202(h1)   \n",
       "1  CONNECTICUT AT METROPOLITAN AVE     ...             55*   \n",
       "\n",
       "                  Article Contributed_To_Accident      Race Gender  \\\n",
       "0  Transportation Article                      No     BLACK      M   \n",
       "1                     NaN                      No  HISPANIC      M   \n",
       "\n",
       "     Driver_City Driver_State DL_State        Arrest_Type Geolocation  \n",
       "0      ROCKVILLE           MD       MD  A - Marked Patrol         NaN  \n",
       "1  SILVER SPRING           MD       MD  A - Marked Patrol         NaN  \n",
       "\n",
       "[2 rows x 39 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_gz.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To read from multiple files you can use the *asterisk* `*`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_month</th>\n",
       "      <th>Stop_day</th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Stop_hr</th>\n",
       "      <th>Stop_min</th>\n",
       "      <th>Stop_sec</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal_Injury</th>\n",
       "      <th>Property_Damage</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Commercial_License</th>\n",
       "      <th>HAZMAT</th>\n",
       "      <th>Commercial_Vehicle</th>\n",
       "      <th>Alcohol</th>\n",
       "      <th>Work_Zone</th>\n",
       "      <th>State</th>\n",
       "      <th>VehicleType</th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Color</th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed_To_Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver_City</th>\n",
       "      <th>Driver_State</th>\n",
       "      <th>DL_State</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Geolocation</th>\n",
       "      <th>Age_of_car</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>2013</td>\n",
       "      <td>17</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>FAILURE TO DISPLAY REGISTRATION CARD UPON DEMA...</td>\n",
       "      <td>WOOTTON PKWAY AND DARNSTOWN ROAD</td>\n",
       "      <td>39.092008</td>\n",
       "      <td>-77.19444</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>NISSAN</td>\n",
       "      <td>350Z</td>\n",
       "      <td>SILVER</td>\n",
       "      <td>Citation</td>\n",
       "      <td>13-409(b)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>MONTGOMERY VILLAGE</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>(39.0920083333333, -77.19444)</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>2013</td>\n",
       "      <td>8</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>5th district, Germantown</td>\n",
       "      <td>DRIVER CHANGING LANES WHEN UNSAFE</td>\n",
       "      <td>RIDGE RD / OBSERVATION DR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>GMC</td>\n",
       "      <td>YUKON</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Citation</td>\n",
       "      <td>21-309(b)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>M</td>\n",
       "      <td>DAMASCUS</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>B - Unmarked Patrol</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_month  Stop_day  Stop_year  Stop_hr  Stop_min  Stop_sec Agency  \\\n",
       "0           3        29       2013       17        34         0    MCP   \n",
       "1           8        12       2013        8        41         0    MCP   \n",
       "\n",
       "                  SubAgency  \\\n",
       "0   1st district, Rockville   \n",
       "1  5th district, Germantown   \n",
       "\n",
       "                                         Description  \\\n",
       "0  FAILURE TO DISPLAY REGISTRATION CARD UPON DEMA...   \n",
       "1                  DRIVER CHANGING LANES WHEN UNSAFE   \n",
       "\n",
       "                           Location    ...                     Article  \\\n",
       "0  WOOTTON PKWAY AND DARNSTOWN ROAD    ...      Transportation Article   \n",
       "1         RIDGE RD / OBSERVATION DR    ...      Transportation Article   \n",
       "\n",
       "   Contributed_To_Accident      Race Gender         Driver_City Driver_State  \\\n",
       "0                       No  HISPANIC      M  MONTGOMERY VILLAGE           MD   \n",
       "1                       No     BLACK      M            DAMASCUS           MD   \n",
       "\n",
       "  DL_State          Arrest_Type                    Geolocation Age_of_car  \n",
       "0       MD    A - Marked Patrol  (39.0920083333333, -77.19444)       10.0  \n",
       "1       MD  B - Unmarked Patrol                            NaN       10.0  \n",
       "\n",
       "[2 rows x 40 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_multiple = bl.Data(\n",
    "    '../Data/Years/TrafficViolations_*.csv.gz')\n",
    "traffic_multiple.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to save traffic data for year 2013 you can call `odo` like this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<odo.backends.csv.CSV at 0x1247e8f60>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo.odo(traffic[traffic.Stop_year == 2013], \n",
    "    '../Data/Years/TrafficViolations_2013.csv.gz')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Interacting with relational databases\n",
    "\n",
    "Let's read the data from the PostGRE SQL database now. The URI for accessing PostGRE SQL database has the following syntax `postgresql://<user_name>:<password>@<server>:<port>/<database>::<table>`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Stop_month</th>\n",
       "      <th>Stop_day</th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Stop_hr</th>\n",
       "      <th>Stop_min</th>\n",
       "      <th>Stop_sec</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal_Injury</th>\n",
       "      <th>Property_Damage</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Commercial_License</th>\n",
       "      <th>HAZMAT</th>\n",
       "      <th>Commercial_Vehicle</th>\n",
       "      <th>Alcohol</th>\n",
       "      <th>Work_Zone</th>\n",
       "      <th>State</th>\n",
       "      <th>VehicleType</th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Color</th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed_To_Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver_City</th>\n",
       "      <th>Driver_State</th>\n",
       "      <th>DL_State</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Geolocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2014</td>\n",
       "      <td>23</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>DRIVER FAILURE TO STOP AT STEADY CIRCULAR RED ...</td>\n",
       "      <td>PARK RD AT HUNGERFORD DR</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>FORD</td>\n",
       "      <td>MUSTANG</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Citation</td>\n",
       "      <td>21-202(h1)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>M</td>\n",
       "      <td>ROCKVILLE</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>2015</td>\n",
       "      <td>23</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>HEADLIGHTS (*)</td>\n",
       "      <td>CONNECTICUT AT METROPOLITAN AVE</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>HONDA</td>\n",
       "      <td>2S</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>ESERO</td>\n",
       "      <td>55*</td>\n",
       "      <td>None</td>\n",
       "      <td>No</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>M</td>\n",
       "      <td>SILVER SPRING</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   index  Stop_month  Stop_day  Stop_year  Stop_hr  Stop_min  Stop_sec Agency  \\\n",
       "0      0           9        30       2014       23        51         0    MCP   \n",
       "1      1           3        31       2015       23        59         0    MCP   \n",
       "\n",
       "                 SubAgency                                        Description  \\\n",
       "0  1st district, Rockville  DRIVER FAILURE TO STOP AT STEADY CIRCULAR RED ...   \n",
       "1   2nd district, Bethesda                                     HEADLIGHTS (*)   \n",
       "\n",
       "      ...          Charge                 Article Contributed_To_Accident  \\\n",
       "0     ...      21-202(h1)  Transportation Article                      No   \n",
       "1     ...             55*                    None                      No   \n",
       "\n",
       "       Race Gender    Driver_City Driver_State DL_State        Arrest_Type  \\\n",
       "0     BLACK      M      ROCKVILLE           MD       MD  A - Marked Patrol   \n",
       "1  HISPANIC      M  SILVER SPRING           MD       MD  A - Marked Patrol   \n",
       "\n",
       "  Geolocation  \n",
       "0        None  \n",
       "1        None  \n",
       "\n",
       "[2 rows x 40 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_psql = bl.Data(\n",
    "    'postgresql://{0}:{1}@localhost:5432/drabast::traffic'\\\n",
    "    .format('drabast', 'pAck7!B0ok')\n",
    ")\n",
    "traffic_psql.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will output traffic violations that involved cars manufactured in 2016 to both, PostGRE SQL and SQLite databases. We will use `odo` to manage the transfers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('traffic_2016', MetaData(bind=Engine(postgresql://drabast:***@localhost:5432/drabast)), Column('index', BigInteger(), table=<traffic_2016>), Column('Stop_month', BigInteger(), table=<traffic_2016>), Column('Stop_day', BigInteger(), table=<traffic_2016>), Column('Stop_year', BigInteger(), table=<traffic_2016>), Column('Stop_hr', BigInteger(), table=<traffic_2016>), Column('Stop_min', BigInteger(), table=<traffic_2016>), Column('Stop_sec', BigInteger(), table=<traffic_2016>), Column('Agency', Text(), table=<traffic_2016>), Column('SubAgency', Text(), table=<traffic_2016>), Column('Description', Text(), table=<traffic_2016>), Column('Location', Text(), table=<traffic_2016>), Column('Latitude', FLOAT(), table=<traffic_2016>), Column('Longitude', FLOAT(), table=<traffic_2016>), Column('Accident', Text(), table=<traffic_2016>), Column('Belts', Text(), table=<traffic_2016>), Column('Personal_Injury', Text(), table=<traffic_2016>), Column('Property_Damage', Text(), table=<traffic_2016>), Column('Fatal', Text(), table=<traffic_2016>), Column('Commercial_License', Text(), table=<traffic_2016>), Column('HAZMAT', Text(), table=<traffic_2016>), Column('Commercial_Vehicle', Text(), table=<traffic_2016>), Column('Alcohol', Text(), table=<traffic_2016>), Column('Work_Zone', Text(), table=<traffic_2016>), Column('State', Text(), table=<traffic_2016>), Column('VehicleType', Text(), table=<traffic_2016>), Column('Year', FLOAT(), table=<traffic_2016>), Column('Make', Text(), table=<traffic_2016>), Column('Model', Text(), table=<traffic_2016>), Column('Color', Text(), table=<traffic_2016>), Column('Violation_Type', Text(), table=<traffic_2016>), Column('Charge', Text(), table=<traffic_2016>), Column('Article', Text(), table=<traffic_2016>), Column('Contributed_To_Accident', Text(), table=<traffic_2016>), Column('Race', Text(), table=<traffic_2016>), Column('Gender', Text(), table=<traffic_2016>), Column('Driver_City', Text(), table=<traffic_2016>), Column('Driver_State', Text(), table=<traffic_2016>), Column('DL_State', Text(), table=<traffic_2016>), Column('Arrest_Type', Text(), table=<traffic_2016>), Column('Geolocation', Text(), table=<traffic_2016>), schema=None)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_2016 = traffic_psql[traffic_psql['Year'] == 2016]\n",
    "\n",
    "# odo.drop('sqlite:///traffic_local.sqlite::traffic2016')\n",
    "# odo.drop('postgresql://drabast:pAck7!B0ok@localhost:5432/drabast::traffic_2016')\n",
    "odo.odo(traffic_2016, 'sqlite:///traffic_local.sqlite::traffic2016')\n",
    "odo.odo(traffic_2016, 'postgresql://drabast:pAck7!B0ok@localhost:5432/drabast::traffic_2016')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reading data from the SQLite database should be trivial by now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Stop_month</th>\n",
       "      <th>Stop_day</th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Stop_hr</th>\n",
       "      <th>Stop_min</th>\n",
       "      <th>Stop_sec</th>\n",
       "      <th>Agency</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Personal_Injury</th>\n",
       "      <th>Property_Damage</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Commercial_License</th>\n",
       "      <th>HAZMAT</th>\n",
       "      <th>Commercial_Vehicle</th>\n",
       "      <th>Alcohol</th>\n",
       "      <th>Work_Zone</th>\n",
       "      <th>State</th>\n",
       "      <th>VehicleType</th>\n",
       "      <th>Year</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Color</th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Article</th>\n",
       "      <th>Contributed_To_Accident</th>\n",
       "      <th>Race</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Driver_City</th>\n",
       "      <th>Driver_State</th>\n",
       "      <th>DL_State</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Geolocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2692</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2015</td>\n",
       "      <td>8</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>DRIVER FAILURE TO OBEY PROPERLY PLACED TRAFFIC...</td>\n",
       "      <td>POOKS HILL RD AT LINDEN AV</td>\n",
       "      <td>39.065777</td>\n",
       "      <td>-77.159442</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>AUDI</td>\n",
       "      <td>4S</td>\n",
       "      <td>SILVER</td>\n",
       "      <td>Warning</td>\n",
       "      <td>21-201(a1)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>NORTH BETHESDA</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>(39.0657766666667, -77.1594416666667)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8862</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>2015</td>\n",
       "      <td>19</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>MCP</td>\n",
       "      <td>2nd district, Bethesda</td>\n",
       "      <td>FAILURE TO STOP AT STOP SIGN</td>\n",
       "      <td>NORFOLK AVE / DEL RAY AVE</td>\n",
       "      <td>38.989567</td>\n",
       "      <td>-77.098130</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>AUDI</td>\n",
       "      <td>A7</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>Warning</td>\n",
       "      <td>21-707(a)</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>M</td>\n",
       "      <td>BETHESDA</td>\n",
       "      <td>MD</td>\n",
       "      <td>MD</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>(38.9895666666667, -77.09813)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   index  Stop_month  Stop_day  Stop_year  Stop_hr  Stop_min  Stop_sec Agency  \\\n",
       "0   2692           4         2       2015        8        31         0    MCP   \n",
       "1   8862           4        11       2015       19        27         0    MCP   \n",
       "\n",
       "                SubAgency                                        Description  \\\n",
       "0  2nd district, Bethesda  DRIVER FAILURE TO OBEY PROPERLY PLACED TRAFFIC...   \n",
       "1  2nd district, Bethesda                       FAILURE TO STOP AT STOP SIGN   \n",
       "\n",
       "                   ...                        Charge                 Article  \\\n",
       "0                  ...                    21-201(a1)  Transportation Article   \n",
       "1                  ...                     21-707(a)  Transportation Article   \n",
       "\n",
       "   Contributed_To_Accident   Race Gender     Driver_City Driver_State  \\\n",
       "0                       No  WHITE      M  NORTH BETHESDA           MD   \n",
       "1                       No  WHITE      M        BETHESDA           MD   \n",
       "\n",
       "  DL_State        Arrest_Type                            Geolocation  \n",
       "0       MD  A - Marked Patrol  (39.0657766666667, -77.1594416666667)  \n",
       "1       MD  A - Marked Patrol          (38.9895666666667, -77.09813)  \n",
       "\n",
       "[2 rows x 40 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_sqlt = bl.Data('sqlite:///traffic_local.sqlite::traffic2016')\n",
    "traffic_sqlt.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Interacting with MongoDB database\n",
    "Reading from MongoDB is very similar to reading from a PostGRE SQL or SQLite databases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Accident</th>\n",
       "      <th>Agency</th>\n",
       "      <th>Alcohol</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Article</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Charge</th>\n",
       "      <th>Color</th>\n",
       "      <th>Commercial_License</th>\n",
       "      <th>Commercial_Vehicle</th>\n",
       "      <th>Contributed_To_Accident</th>\n",
       "      <th>DL_State</th>\n",
       "      <th>Description</th>\n",
       "      <th>Driver_City</th>\n",
       "      <th>Driver_State</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Geolocation</th>\n",
       "      <th>HAZMAT</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Location</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Make</th>\n",
       "      <th>Model</th>\n",
       "      <th>Personal_Injury</th>\n",
       "      <th>Property_Damage</th>\n",
       "      <th>Race</th>\n",
       "      <th>State</th>\n",
       "      <th>Stop_day</th>\n",
       "      <th>Stop_hr</th>\n",
       "      <th>Stop_min</th>\n",
       "      <th>Stop_month</th>\n",
       "      <th>Stop_sec</th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>SubAgency</th>\n",
       "      <th>VehicleType</th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Work_Zone</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>No</td>\n",
       "      <td>MCP</td>\n",
       "      <td>No</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>21-201(a1)</td>\n",
       "      <td>RED</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>DRIVER FAILURE TO OBEY PROPERLY PLACED TRAFFIC...</td>\n",
       "      <td>TAKOMA PARK</td>\n",
       "      <td>MD</td>\n",
       "      <td>No</td>\n",
       "      <td>M</td>\n",
       "      <td>(39.00438825, -77.0061869666667)</td>\n",
       "      <td>No</td>\n",
       "      <td>39.004388</td>\n",
       "      <td>FLOWER AVE AT WAYNE AVE</td>\n",
       "      <td>-77.006187</td>\n",
       "      <td>VOLK</td>\n",
       "      <td>4S</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MD</td>\n",
       "      <td>16</td>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "      <td>3rd district, Silver Spring</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>Citation</td>\n",
       "      <td>No</td>\n",
       "      <td>2011.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>No</td>\n",
       "      <td>MCP</td>\n",
       "      <td>No</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>Transportation Article</td>\n",
       "      <td>No</td>\n",
       "      <td>13-401(h)</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>MD</td>\n",
       "      <td>DRIVING VEHICLE ON HIGHWAY WITH SUSPENDED REGI...</td>\n",
       "      <td>GERMANTOWN</td>\n",
       "      <td>MD</td>\n",
       "      <td>No</td>\n",
       "      <td>M</td>\n",
       "      <td>(39.0821319333333, -77.1463659333333)</td>\n",
       "      <td>No</td>\n",
       "      <td>39.082132</td>\n",
       "      <td>355 / VEIRS MILL</td>\n",
       "      <td>-77.146366</td>\n",
       "      <td>CADILAC</td>\n",
       "      <td>DEVILLE</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>MD</td>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2013</td>\n",
       "      <td>1st district, Rockville</td>\n",
       "      <td>02 - Automobile</td>\n",
       "      <td>Citation</td>\n",
       "      <td>No</td>\n",
       "      <td>2004.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  Accident Agency Alcohol        Arrest_Type                 Article Belts  \\\n",
       "0       No    MCP      No  A - Marked Patrol  Transportation Article    No   \n",
       "1       No    MCP      No  A - Marked Patrol  Transportation Article    No   \n",
       "\n",
       "       Charge  Color Commercial_License Commercial_Vehicle   ...   Stop_hr  \\\n",
       "0  21-201(a1)    RED                 No                 No   ...        21   \n",
       "1   13-401(h)  BLACK                 No                 No   ...        17   \n",
       "\n",
       "  Stop_min Stop_month Stop_sec Stop_year                    SubAgency  \\\n",
       "0       46          7        0      2013  3rd district, Silver Spring   \n",
       "1       55          1        0      2013      1st district, Rockville   \n",
       "\n",
       "       VehicleType Violation_Type Work_Zone    Year  \n",
       "0  02 - Automobile       Citation        No  2011.0  \n",
       "1  02 - Automobile       Citation        No  2004.0  \n",
       "\n",
       "[2 rows x 39 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_mongo = bl.Data('mongodb://localhost:27017/packt::traffic')\n",
    "traffic_mongo.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are two ways of accessing columns: you can get a single column at a time by accessing them as if they were a DataShape attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2003.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     Year\n",
       "0  2014.0\n",
       "1  2003.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic.Year.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or indexing; this allows to select more than one column at a time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>Year</th>\n",
       "      <th>Accident</th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Alcohol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PARK RD AT HUNGERFORD DR</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CONNECTICUT AT METROPOLITAN AVE</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "                          Location    Year Accident Fatal Alcohol\n",
       "0         PARK RD AT HUNGERFORD DR  2014.0       No    No      No\n",
       "1  CONNECTICUT AT METROPOLITAN AVE  2003.0       No    No      No"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(traffic[['Location', 'Year', 'Accident', 'Fatal', 'Alcohol']]\n",
    "    .head(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Symbolic transformations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we could not reflect the schema from an already existing object, we would have to specify the schema manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "schema_example = bl.symbol('schema_exampl', \n",
    "                           '{id: int, name: string}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we already have an existing dataset `traffic`, we can reuse the schema by calling `traffic.dshape` and specify our transformations directly on it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "traffic_s = bl.symbol('traffic', traffic.dshape)\n",
    "traffic_2013 = traffic_s[traffic_s['Stop_year'] == 2013][\n",
    "    ['Stop_year', 'Arrest_Type','Color', 'Charge']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To present how this works, let's read our dataset into pandas' DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "traffic_pd = pd.read_csv('../Data/TrafficViolations.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can now pass the dataset directly to `traffic_2013` object and perform the computation using the `.compute(...)` method of Blaze."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Arrest_Type</th>\n",
       "      <th>Color</th>\n",
       "      <th>Charge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>2013</td>\n",
       "      <td>A - Marked Patrol</td>\n",
       "      <td>SILVER</td>\n",
       "      <td>13-409(b)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>2013</td>\n",
       "      <td>B - Unmarked Patrol</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>21-309(b)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Stop_year          Arrest_Type   Color     Charge\n",
       "73        2013    A - Marked Patrol  SILVER  13-409(b)\n",
       "215       2013  B - Unmarked Patrol   BLACK  21-309(b)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bl.compute(traffic_2013, traffic_pd).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also pass a list of lists or a list of NumPy arrays."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[2013, 'A - Marked Patrol', 'SILVER', '13-409(b)'],\n",
       "       [2013, 'B - Unmarked Patrol', 'BLACK', '21-309(b)']], dtype=object)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bl.compute(traffic_2013, traffic_pd.values)[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Operations on columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can check that by getting all the distinct values for the `Stop_year` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_year\n",
       "2       2013\n",
       "0       2014\n",
       "1       2015\n",
       "3       2016"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic['Stop_year'].distinct().sort()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can subtract 2000 from the `Stop_year` column as we do not need a greater detail. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_year\n",
       "0         14\n",
       "1         15"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic['Stop_year'].head(2) - 2000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you wanted to log-transform the `Stop_year` you need to"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.607878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.608374</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_year\n",
       "0   7.607878\n",
       "1   7.608374"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bl.log(traffic['Stop_year']).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reducing data\n",
    "Some reduction methods are also available, like `.mean()` (that calculates the average), `.std` (that calculates standard deviation) or `.max()` (that returns the maximum from the list)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "2016"
      ],
      "text/plain": [
       "2016"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic['Stop_year'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can calculate the age of the car (in years) at the time when the violation occured"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stop_year</th>\n",
       "      <th>Year</th>\n",
       "      <th>Age_of_car</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Stop_year    Year  Age_of_car\n",
       "0       2014  2014.0         0.0\n",
       "1       2015  2003.0        12.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic = bl.transform(traffic, \n",
    "             Age_of_car = traffic.Stop_year - traffic.Year)\n",
    "\n",
    "traffic.head(2)[['Stop_year', 'Year', 'Age_of_car']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and to calculate the average age of the car involved in a fatal trafic violation and count the number of occurences you can use the `by` operation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fatal</th>\n",
       "      <th>Fatal_AvgAge</th>\n",
       "      <th>Fatal_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>No</td>\n",
       "      <td>9.580998</td>\n",
       "      <td>404418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Yes</td>\n",
       "      <td>8.798246</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  Fatal  Fatal_AvgAge  Fatal_Count\n",
       "0    No      9.580998       404418\n",
       "1   Yes      8.798246          116"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bl.by(traffic['Fatal'], \n",
    "      Fatal_AvgAge=traffic.Age_of_car.mean(),\n",
    "      Fatal_Count =traffic.Age_of_car.count()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joins\n",
    "We first select all the traffic violations by violation type (the `violation` object) and the traffic violations involving belts (the `belts` object). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "violation = traffic[\n",
    "    ['Stop_month','Stop_day','Stop_year',\n",
    "     'Stop_hr','Stop_min','Stop_sec','Violation_Type']]\n",
    "\n",
    "belts = traffic[\n",
    "    ['Stop_month','Stop_day','Stop_year',\n",
    "     'Stop_hr','Stop_min','Stop_sec','Belts']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we join the two objects on the six date and time columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "violation_belts = bl.join(violation, belts, \n",
    "      ['Stop_month','Stop_day','Stop_year',\n",
    "       'Stop_hr','Stop_min','Stop_sec'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have the full dataset in place, let's check how many traffic violations involved belts and what sort of punishment was issued to the driver."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Violation_Type</th>\n",
       "      <th>Belts</th>\n",
       "      <th>Violation_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Citation</td>\n",
       "      <td>No</td>\n",
       "      <td>989728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Warning</td>\n",
       "      <td>No</td>\n",
       "      <td>439490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ESERO</td>\n",
       "      <td>No</td>\n",
       "      <td>56447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Citation</td>\n",
       "      <td>Yes</td>\n",
       "      <td>35596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Warning</td>\n",
       "      <td>Yes</td>\n",
       "      <td>12245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ESERO</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SERO</td>\n",
       "      <td>No</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "  Violation_Type Belts  Violation_count\n",
       "0       Citation    No           989728\n",
       "5        Warning    No           439490\n",
       "2          ESERO    No            56447\n",
       "1       Citation   Yes            35596\n",
       "6        Warning   Yes            12245\n",
       "3          ESERO   Yes             1327\n",
       "4           SERO    No                3"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bl.by(violation_belts[['Violation_Type', 'Belts']],\n",
    "      Violation_count=violation_belts.Belts.count()\n",
    ").sort('Violation_count', ascending=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
